import MySQLdb, sys, dataFormat

class MysqlAgent:    

    def __init__(self, sqlConfigDict):
        self.requestIdList = []
        self.randomIdList = []
        try:
            self.conn = MySQLdb.connect(**sqlConfigDict)
            self.cursor = self.conn.cursor()
        except MySQLdb.Error, e:
            sys.stderr.write("Error %d: %s"%e.args)
            sys.exit(1)
        return
        
    def __del__(self):
        try:
            self.conn.commit()
        except MySQLdb.Error, e:
            sys.stderr.write("Error %d: %s"%e.args)
            sys.exit(1)
            self.cursor.close()
            self.conn.close()
        self.cursor.close()
        self.conn.close()
        print 'MysqlAgent released.'
        return
    
    def getDataNodeDict(self, amount = None):
        """
        TrainingRecordFormat: reqID(int),textStr(str),classFreqDist(list of str)
        return a dict which is indexing a set of training records.
        """
        import instanceFactory
        di = {}
        if amount != None:
            limit = "LIMIT %d"%(amount,)
        else:
            limit = ''
        self.cursor.execute("""                                
                            SELECT requestID, ResultText
                            FROM resulttext_table AS rtt
                            %s;
                            """%limit)
        import copy
        
        while self.cursor.rownumber < self.cursor.rowcount:        
            row = self.cursor.fetchone()  
            preprocessor = instanceFactory.InstanceFactory.getInstance().getPreprocessor()
            tn = dataFormat.TextNode(preproc = preprocessor)
            tn.reqID = int(row[0])
            tn.textStr = str(row[1])
            cursor_copy = copy.copy(self.cursor)            
            self.cursor.execute("""                                
                            SELECT SnomedCode
                            FROM resultdetails_table AS rdt
                            WHERE RequestID=%s
                            """%(tn.reqID,))
            for classValue in self.cursor.fetchall():
                tn.classFreqDist.inc(classValue[0])            
            di[tn.reqID]=tn
            self.cursor = cursor_copy
        return di 
    
    def getRandomDataNodeDict(self, focusedClassFilename, randomConfigDict):
        """
        TrainingRecordFormat: reqID(int),textStr(str),classFreqDist(list of str)
        return a dict which is indexing a set of training records.
        """
        import os
        subsetFilename = randomConfigDict['subsetFile']
        focusedClassList = None
        if os.path.exists(focusedClassFilename):
            focusedClassList = open(focusedClassFilename,'rU').read().split()        
        if randomConfigDict['needRerandom'] == False and os.path.exists(subsetFilename):
            self.randomIdList = open(subsetFilename,'rU').read().split()
        else:
            if len(self.requestIdList) == 0:
                import histogram
                self.requestIdList = histogram.readRequestIdList(self, randomConfigDict['randomRange'])
                recordFilename = randomConfigDict['baseFile']
                open(recordFilename,'w').write('\n'.join(["%d"%v for v in self.requestIdList]))
                print("IDs are saved in file '%s'"%recordFilename)    
            if len(self.randomIdList) == 0:
                import random
                random.seed(1)
            amount = randomConfigDict['randomAmount']
            if amount > 0:
                self.randomIdList = sorted(random.sample(self.requestIdList, amount))
            else:
                self.randomIdList = sorted(self.requestIdList)
            open(subsetFilename,'w').write('\n'.join(["%d"%v for v in self.randomIdList]))        
            print("%s is saved."%subsetFilename)
        return self.getDataNodeDictByIdList(self.randomIdList, focusedClassList)
        
    def getDataNodeDictByIdList(self, IdList, focusedClassList):
        import instanceFactory         
        di = {}        
        for id in self.randomIdList:
            self.cursor.execute("""                                
                                SELECT requestID, ResultText
                                FROM resulttext_table AS rtt
                                WHERE requestID = %s
                                """%id)       
            row = self.cursor.fetchone()
            preprocessor = instanceFactory.InstanceFactory.getInstance().getPreprocessor()
            tn = dataFormat.TextNode(preproc = preprocessor)
            tn.reqID = int(row[0])
            tn.textStr = str(row[1])         
            self.cursor.execute("""                                
                            SELECT SnomedCode
                            FROM resultdetails_table AS rdt
                            WHERE RequestID=%s
                            """%(tn.reqID,))
            classValues = self.cursor.fetchall()
            for classValue in classValues:
                if focusedClassList != None:
                    if classValue[0] not in focusedClassList:
                        tn.classFreqDist.inc(focusedClassList[-1])
                    else:
                        tn.classFreqDist.inc(classValue[0])
                else:
                    tn.classFreqDist.inc(classValue[0])                            
            di[tn.reqID]=tn       
        return di 

    def getSchemas(self):
        """
        shows DESCRIPTIONs of all tables residing in this database
        """
        self.cursor.execute("SHOW TABLES")
        tableNames = self.cursor.fetchall()
        schemas = []
        for tablename in tableNames:
            self.cursor.execute("DESCRIBE %s" %tablename)
            schemas.append(self.cursor.fetchall())
        return schemas
        
        
    def createTableUseSchema(self, tableName, columnsDescription, engineType = "InnoDB"):
        self.cursor.execute("DROP TABLE IF EXISTS %s"%tableName)
        tableSchema = """ CREATE TABLE IF NOT EXISTS %s ( %s ) ENGINE = %s """%(tableName, columnsDescription, engineType)
        self.cursor.execute(tableSchema)
        self.conn.commit()
        return        
    
        
    def saveIntoTable(self, tableName, columnNameList, valueList):
        insertStatement =   " INSERT INTO " + tableName + "(" + ','.join(columnNameList) + ")" + \
                            " VALUES " + "(" + ','.join(["\"%s\""%str(v) for v in valueList])+ ")"  
        self.cursor.execute(insertStatement)
        self.conn.commit()
        return
        
    def getMostFreqAttr(self, attrName, tableName, amount):
        statement = """
        SELECT %s, COUNT(*) AS occurs FROM %s GROUP BY %s ORDER BY occurs DESC LIMIT %s;
        """%(attrName, tableName, attrName, amount)
        self.cursor.execute(statement)        
        return self.cursor.fetchall()

if __name__ == "__main__":
    ma = MysqlAgent()
    di = ma.getRandomDataNodeDict(10000)
    print len(di)
    